{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4d2a688e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import timedelta\n",
    "import numpy as np\n",
    "import os\n",
    "import pandas as pd\n",
    "import re\n",
    "\n",
    "\n",
    "\n",
    "rebuild_csv               = True\n",
    "show_csv_summary          = False\n",
    "data_directory            = './data-uk/'\n",
    "primary_market_csv_import = f'{data_directory}20230322 - Gilt Issuance History.csv'\n",
    "primary_market_csv_export = f'{data_directory}gilt-export.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c9c43a1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date range is 2021-03-03 00:00:00 -- 2023-03-22 00:00:00\n"
     ]
    }
   ],
   "source": [
    "if rebuild_csv:\n",
    "    gilt_data = []\n",
    "    \n",
    "    with open( primary_market_csv_import, 'r' ) as file:\n",
    "        for line in file:\n",
    "            cells = line.split( ',' )\n",
    "            \n",
    "            # line should have data\n",
    "            if len( cells ) == 0 or cells[0] == '':\n",
    "                continue\n",
    "                \n",
    "            # want only conventional gilts\n",
    "            if not re.search( r'% Treasury Gilt', cells[0] ):\n",
    "                continue\n",
    "                \n",
    "            # want only auctions\n",
    "            if cells[3] != 'Outright':\n",
    "                continue\n",
    "                \n",
    "            # record gilt data\n",
    "            isin_code   = cells[1]\n",
    "            sale_date   = cells[2]\n",
    "            clean_price = cells[4]\n",
    "            issue_yield = cells[5]\n",
    "            issue_size  = cells[6]\n",
    "            \n",
    "            # make sure there is price data\n",
    "            if clean_price == 'N/A' or issue_yield == 'N/A':\n",
    "                continue\n",
    "            \n",
    "            gilt_info = {\n",
    "                'isin_code':   isin_code,\n",
    "                'sale_date':   sale_date,\n",
    "                'clean_price': clean_price,\n",
    "                'issue_yield': issue_yield,\n",
    "                'issue_size':  issue_size\n",
    "            }\n",
    "                \n",
    "            gilt_data.append( gilt_info )\n",
    "            \n",
    "    gilt_df = pd.DataFrame.from_records( gilt_data )\n",
    "    gilt_df['sale_date'] = pd.to_datetime( gilt_df['sale_date'] )\n",
    "    gilt_df.to_csv( primary_market_csv_export, index = False )\n",
    "    \n",
    "#     print( 'The following Gilts have good data:' )\n",
    "#     print()\n",
    "#     print( '\\n'.join( [ f'* {isin_code}' for isin_code in gilt_df['isin_code'].unique() ] ) )\n",
    "#     print()\n",
    "#     print( f\"[summary: {len( gilt_df )} auctions; {len( gilt_df['isin_code'].unique() )} securities]\" )\n",
    "\n",
    "gilt_df = pd.read_csv( primary_market_csv_export, index_col = False )\n",
    "gilt_df['sale_date'] = pd.to_datetime( gilt_df['sale_date'] )\n",
    "print( f\"Date range is {np.min( gilt_df['sale_date'] )} -- {np.max( gilt_df['sale_date'] )}\" )\n",
    "\n",
    "if show_csv_summary:\n",
    "    display(\n",
    "        gilt_df \\\n",
    "            .groupby( 'isin_code' ) \\\n",
    "            .count()[['clean_price']] \\\n",
    "            .rename( columns = { 'clean_price': 'count' } ) \\\n",
    "            .sort_values( 'count', ascending = False ) \\\n",
    "            .reset_index()\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "987938a0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Could not load data for GB00BFWFPP71 (1 auctions).\n",
      "Could not load data for GB00BMBL1G81 (1 auctions).\n",
      "Could not load data for GB00BK5CVX03 (1 auctions).\n",
      "Could not load data for GB00BJLR0J16 (1 auctions).\n",
      "Could not load data for GB00BPCJD997 (1 auctions).\n",
      "Could not load data for GB00BQC4R999 (1 auctions).\n"
     ]
    }
   ],
   "source": [
    "secondary_market_df = None\n",
    "auction_counts      = gilt_df \\\n",
    "    .groupby( 'isin_code' ) \\\n",
    "    .count()[['clean_price']] \\\n",
    "    .rename( columns = { 'clean_price': 'count' } ) \\\n",
    "    .sort_values( 'count', ascending = False ) \\\n",
    "    .reset_index()\n",
    "\n",
    "for ix, row in auction_counts.iterrows():\n",
    "    isin_code = row['isin_code']\n",
    "    filename  = f'{data_directory}isin-{isin_code}.csv'\n",
    "    \n",
    "    if not os.path.exists( filename ):\n",
    "        print( f\"Could not load data for {isin_code} ({row['count']} auctions).\" )\n",
    "        continue\n",
    "        \n",
    "    isin_df = pd.read_csv( filename ).rename( columns = {\n",
    "        'Close of Business Date': 'date',\n",
    "        'ISIN':                   'isin_code',\n",
    "        'Clean Price':            'clean_price_secondary',\n",
    "        'Dirty Price':            'dirty_price_secondary',\n",
    "        'Yield':                  'yield_secondary'\n",
    "    } )[['date','isin_code','clean_price_secondary','dirty_price_secondary','yield_secondary']]\n",
    "    isin_df['date'] = pd.to_datetime( isin_df['date'] )\n",
    "    \n",
    "    if secondary_market_df is None:\n",
    "        secondary_market_df = isin_df\n",
    "        \n",
    "    else:\n",
    "        secondary_market_df = pd.concat( [ secondary_market_df, isin_df ], axis = 0 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f60b8ea8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SUMMARY STATS (date shift -1):\n",
      "    -----\n",
      "    Observations - - - - - - - - - - - - - - - - - - - -  92\n",
      "    Auction volume - - - - - - - - - - - - - - - - - - - £264,203 (millions)\n",
      "    Avg price spread (secondary - primary) - - - - - - - £0.0836\n",
      "    Weighted avg price spread (secondary - primary)  - - £0.0730\n",
      "    Avg price spread (secondary - primary), %  - - - - -  0.091%\n",
      "    Weighted avg price spread (secondary - primary), % -  0.084%\n",
      "    Avg yield spread (primary - secondary) - - - - - - -  0.2760bp\n",
      "    Weighted avg yield spread (primary - secondary)  - -  0.5486bp\n",
      "    \n",
      "\n",
      "    SUMMARY STATS (date shift 0):\n",
      "    -----\n",
      "    Observations - - - - - - - - - - - - - - - - - - - -  99\n",
      "    Auction volume - - - - - - - - - - - - - - - - - - - £289,200 (millions)\n",
      "    Avg price spread (secondary - primary) - - - - - - - £0.0801\n",
      "    Weighted avg price spread (secondary - primary)  - - £0.1019\n",
      "    Avg price spread (secondary - primary), %  - - - - -  0.057%\n",
      "    Weighted avg price spread (secondary - primary), % -  0.084%\n",
      "    Avg yield spread (primary - secondary) - - - - - - -  1.1360bp\n",
      "    Weighted avg yield spread (primary - secondary)  - -  1.7721bp\n",
      "    \n",
      "\n",
      "    SUMMARY STATS (date shift 1):\n",
      "    -----\n",
      "    Observations - - - - - - - - - - - - - - - - - - - -  91\n",
      "    Auction volume - - - - - - - - - - - - - - - - - - - £260,539 (millions)\n",
      "    Avg price spread (secondary - primary) - - - - - - - £0.1659\n",
      "    Weighted avg price spread (secondary - primary)  - - £0.1354\n",
      "    Avg price spread (secondary - primary), %  - - - - -  0.120%\n",
      "    Weighted avg price spread (secondary - primary), % -  0.106%\n",
      "    Avg yield spread (primary - secondary) - - - - - - -  0.6840bp\n",
      "    Weighted avg yield spread (primary - secondary)  - -  0.8598bp\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "# Reload the DF again (so that the cell works fine)\n",
    "date_shift_df = pd.DataFrame()\n",
    "for date_shift in range( -1, 2 ):\n",
    "    date_shift = int( date_shift )\n",
    "    gilt_df = pd.read_csv( primary_market_csv_export, index_col = False )\n",
    "    gilt_df['sale_date'] = pd.to_datetime( gilt_df['sale_date'] ) + timedelta( days = date_shift )\n",
    "\n",
    "    gilt_df = gilt_df.merge(\n",
    "        secondary_market_df,\n",
    "        left_on  = [ 'sale_date', 'isin_code' ],\n",
    "        right_on = [ 'date', 'isin_code' ],\n",
    "        how      = 'left'\n",
    "    )\n",
    "    gilt_df = gilt_df.assign(\n",
    "        clean_price_spread     = gilt_df['clean_price_secondary'] - gilt_df['clean_price'],\n",
    "        yield_spread           = gilt_df['issue_yield'] - gilt_df['yield_secondary'], # note that these deltas are not in the same order!\n",
    "        clean_price_spread_pct = ( gilt_df['clean_price_secondary'] - gilt_df['clean_price'] ) / gilt_df['clean_price_secondary']\n",
    "    )\n",
    "    \n",
    "    nonnan_mask             = ~np.isnan( gilt_df['date'] )\n",
    "    gilt_df                 = gilt_df[nonnan_mask]\n",
    "    weighted_average_spread = np.average( gilt_df['clean_price_spread_pct'], weights = gilt_df['issue_size'] )\n",
    "    date_shift_df           = pd.concat(\n",
    "        [\n",
    "            date_shift_df,\n",
    "            pd.DataFrame.from_records( [ { 'date_shift': date_shift, 'spread': weighted_average_spread } ] )\n",
    "        ],\n",
    "        axis = 0,\n",
    "        ignore_index = True\n",
    "    )\n",
    "\n",
    "    print( f'''\n",
    "    SUMMARY STATS (date shift {date_shift}):\n",
    "    -----\n",
    "    Observations - - - - - - - - - - - - - - - - - - - -  {len( gilt_df )}\n",
    "    Auction volume - - - - - - - - - - - - - - - - - - - £{int( np.sum( gilt_df['issue_size'] ) ):,} (millions)\n",
    "    Avg price spread (secondary - primary) - - - - - - - £{np.mean( gilt_df['clean_price_spread'] ):0.04f}\n",
    "    Weighted avg price spread (secondary - primary)  - - £{np.average( gilt_df['clean_price_spread'], weights = gilt_df['issue_size'] ):0.04f}\n",
    "    Avg price spread (secondary - primary), %  - - - - -  {np.mean( gilt_df['clean_price_spread_pct'] ):0.03%}\n",
    "    Weighted avg price spread (secondary - primary), % -  {weighted_average_spread:0.03%}\n",
    "    Avg yield spread (primary - secondary) - - - - - - -  {np.mean( gilt_df['yield_spread'] ) * 100:0.04f}bp\n",
    "    Weighted avg yield spread (primary - secondary)  - -  {np.average( gilt_df['yield_spread'], weights = gilt_df['issue_size'] ) * 100:0.04f}bp\n",
    "    ''' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2a52f39",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
